import time
import json
from datetime import datetime
from sp_api.api import Products
from sp_api.base import Marketplaces, Credentials
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from app.intereface_amazon.databasemodel import AmazonProduct, Base
from app.intereface_amazon.amazon_config import SP_API_CREDENTIALS, MARKETPLACE_CONFIG, DB_CONFIG

class AmazonProductUploader:
    def __init__(self, marketplace_id='ATVPDKIKX0DER', db_url=None):
        """
        初始化亚马逊产品上传器
        
        参数:
            marketplace_id: 市场ID，默认为美国市场
            db_url: 数据库连接URL
        """
        self.marketplace_id = marketplace_id
        self.marketplace = Marketplaces.US  # 默认使用美国市场
        
        # 设置认证信息
        self.credentials = Credentials(
            refresh_token=SP_API_CREDENTIALS['refresh_token'],
            lwa_client_id=SP_API_CREDENTIALS['lwa_client_id'],
            lwa_client_secret=SP_API_CREDENTIALS['lwa_client_secret'],
            aws_access_key=SP_API_CREDENTIALS['aws_access_key'],
            aws_secret_key=SP_API_CREDENTIALS['aws_secret_key'],
            role_arn=SP_API_CREDENTIALS['role_arn']
        )
        
        # 初始化Products API
        self.products_api = Products(
            marketplace=self.marketplace,
            credentials=self.credentials
        )
        
        # 初始化数据库连接
        if db_url:
            self.engine = create_engine(db_url)
        else:
            self.engine = create_engine(DB_CONFIG['url'])
        Base.metadata.create_all(self.engine)
        self.Session = sessionmaker(bind=self.engine)
    
    def upload_products(self, product_data_list, batch_size=5):
        """
        批量上传产品到亚马逊
        
        参数:
            product_data_list: 产品数据列表，每个元素是一个字典
            batch_size: 每批处理的产品数量，默认为5
        
        返回:
            上传结果列表
        """
        upload_results = []
        session = self.Session()
        
        try:
            for i in range(0, len(product_data_list), batch_size):
                batch = product_data_list[i:i+batch_size]
                
                try:
                    # 准备产品数据
                    products = []
                    for product in batch:
                        # 转换产品数据为API格式
                        api_product = self._convert_to_api_format(product)
                        products.append(api_product)
                    
                    # 调用Products API上传产品
                    response = self.products_api.create_product(
                        body={
                            'products': products
                        }
                    )
                    
                    # 处理响应
                    if response.payload:
                        # 保存到数据库
                        for product in batch:
                            try:
                                # 检查产品是否已存在
                                existing_product = session.query(AmazonProduct).filter_by(sku=product['sku']).first()
                                
                                if existing_product:
                                    # 更新现有产品
                                    for key, value in product.items():
                                        setattr(existing_product, key, value)
                                    existing_product.updated_at = datetime.now()
                                else:
                                    # 创建新产品
                                    new_product = AmazonProduct(**product)
                                    session.add(new_product)
                                
                                upload_results.append({
                                    'sku': product['sku'],
                                    'status': 'success'
                                })
                                
                            except Exception as e:
                                upload_results.append({
                                    'sku': product.get('sku', 'unknown'),
                                    'status': 'error',
                                    'message': str(e)
                                })
                                print(f"保存产品到数据库时出错: {e}")
                                continue
                        
                        # 提交事务
                        session.commit()
                    else:
                        upload_results.append({
                            'batch': i // batch_size + 1,
                            'status': 'error',
                            'message': 'No payload in response'
                        })
                    
                    # 添加延迟以避免API限制
                    time.sleep(2)
                    
                except Exception as e:
                    session.rollback()
                    upload_results.append({
                        'batch': i // batch_size + 1,
                        'status': 'error',
                        'message': str(e)
                    })
                    print(f"上传产品批次时出错: {e}")
                    continue
                
        finally:
            session.close()
        
        return upload_results
    
    def _convert_to_api_format(self, product):
        """
        将产品数据转换为API格式
        
        参数:
            product: 产品数据字典
        
        返回:
            API格式的产品数据
        """
        # 提取属性
        attributes = {}
        
        # 基本属性
        if 'title' in product:
            attributes['title'] = product['title']
        if 'brand' in product:
            attributes['brand'] = product['brand']
        if 'description' in product:
            attributes['description'] = product['description']
        if 'item_type' in product:
            attributes['item_type'] = product['item_type']
        if 'manufacturer' in product:
            attributes['manufacturer'] = product['manufacturer']
        if 'part_number' in product:
            attributes['part_number'] = product['part_number']
        if 'product_tax_code' in product:
            attributes['product_tax_code'] = product['product_tax_code']
        
        # 价格
        if 'standard_price' in product and 'currency' in product:
            attributes['standard_price'] = {
                'amount': float(product['standard_price']),
                'currency': product['currency']
            }
        
        # 要点
        if 'bullet_points' in product:
            if isinstance(product['bullet_points'], str):
                try:
                    bullet_points = json.loads(product['bullet_points'])
                except:
                    bullet_points = [product['bullet_points']]
            else:
                bullet_points = product['bullet_points']
            attributes['bullet_point'] = bullet_points
        
        # 构建API格式的产品数据
        api_product = {
            'sku': product['sku'],
            'productType': product.get('product_type', 'PRODUCT'),
            'requirements': 'LISTING',
            'attributes': attributes
        }
        
        return api_product
    
    def save_results_to_file(self, results, output_file='upload_results.json'):
        """
        将上传结果保存到JSON文件
        
        参数:
            results: 上传结果列表
            output_file: 输出文件名
        """
        with open(output_file, 'w', encoding='utf-8') as f:
            json.dump(results, f, indent=4)
        
        print(f"上传结果已保存到 {output_file}")

# 使用示例
if __name__ == "__main__":
    # 数据库连接URL
    db_url = "mysql+pymysql://username:password@localhost:3306/database_name"
    
    # 创建上传器实例
    uploader = AmazonProductUploader(db_url=db_url)
    
    # 示例产品数据
    product_data_list = [
        {
            'sku': 'TEST-SKU-001',
            'product_type': 'PRODUCT',
            'title': 'Test Product 1',
            'brand': 'Test Brand',
            'bullet_points': ['Feature 1', 'Feature 2'],
            'description': 'This is a test product description.',
            'item_type': 'test-item-type',
            'manufacturer': 'Test Manufacturer',
            'part_number': 'TEST-PART-001',
            'product_tax_code': 'A_GEN_TAX',
            'standard_price': 19.99,
            'currency': 'USD'
        }
    ]
    
    # 上传产品
    upload_results = uploader.upload_products(product_data_list)
    
    # 保存结果
    uploader.save_results_to_file(upload_results) 